#!/use/bin/python
# coding=utf-8
# 将  大数据统计的数据 保存到数据库中
import datetime
import pymysql
import requests
import json

from dbutils.pooled_db import PooledDB

# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    # pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# 调用大数据接口 获取开机率提升
def lifeAssistantStartUpRatio():

    url = "http://192.168.2.176:8080/star-rocks/api/v1/query-service?token=93da758169824120a3087065"

    payload = json.dumps({
        "modelId": "lifeAssistantStartUpRatio",
        "requireParams": {
            "endDt": f"{today}"
        }
    })
    headers = {
        'Content-Type': 'application/json'
    }

    response = requests.request("POST", url, headers=headers, data=payload)

    dataJson = json.loads(response.text)
    lifeRateData = dataJson['data'][0]
    startDt = lifeRateData['startDt']
    endDt = lifeRateData['endDt']
    lifeTotalUv = lifeRateData['lifeTotalUv']
    lifeKjUv = lifeRateData['lifeKjUv']
    notLifeTotalUv = lifeRateData['notLifeTotalUv']
    notLifeKjUv = lifeRateData['notLifeKjUv']
    lifeKjRate = round(lifeKjUv*100/lifeTotalUv,2)
    notLifeKjRate = round(notLifeKjUv*100/notLifeTotalUv,2)
    lifeKjRateUp = round(lifeKjRate - notLifeKjRate,2)
    print(f"每日开机率提升-开始时间:{startDt},结束时间:{endDt},生活助手总设备数:{lifeTotalUv},生活助手开机设备量:{lifeKjUv},非生活助手总设备量:{notLifeTotalUv},非生活助手开机设备量:{notLifeKjUv}"
          f"生活助手开机率:{lifeKjRate},非生活助手开机率:{notLifeKjRate},开机率提升:{lifeKjRateUp}")
    #print(lifeRateData)
    #print(dataJson)
    #报表key - 存储所有开机率数据
    analysisKey = "StartUpRatioAll"
    #saveJsonDb(analysisKey,'lifeTotalUv','生活助手总设备数',lifeTotalUv,endDt)
    #saveJsonDb(analysisKey, 'lifeKjUv', '生活助手开机设备数', lifeKjUv, endDt)
    #saveJsonDb(analysisKey, 'notLifeTotalUv', '非生活助手总设备数', notLifeTotalUv, endDt)
    #saveJsonDb(analysisKey, 'notLifeKjUv', '非生活助手开机设备数', notLifeKjUv, endDt)
    #saveJsonDb(analysisKey, 'lifeKjRate', '生活助手开机率', lifeKjRate, endDt)
    #saveJsonDb(analysisKey, 'notLifeKjRate', '非生活助手开机率', notLifeKjRate, endDt)
    #saveJsonDb(analysisKey, 'lifeKjRateUp', '开机率提升', lifeKjRateUp, endDt)
    # 报表key - 存储 生活助手 跟 非生活助手 开机率
    analysisKey = "StartUpRatio"
    saveJsonDb(analysisKey, 'lifeKjRate', '生活助手', lifeKjRate, endDt)
    saveJsonDb(analysisKey, 'notLifeKjRate', '大盘', notLifeKjRate, endDt)

# 调用大数据接口 获取开机频次提升
def lifeAssistantStartUpFrequency():

    url = "http://192.168.2.176:8080/star-rocks/api/v1/query-service?token=93da758169824120a3087065"

    payload = json.dumps({
        "modelId": "lifeAssistantStartUpFrequency",
        "requireParams": {
            "endDt": f"{today}"
        }
    })
    headers = {
        'Content-Type': 'application/json'
    }

    response = requests.request("POST", url, headers=headers, data=payload)

    dataJson = json.loads(response.text)
    lifeRateData = dataJson['data'][0]
    startDt = lifeRateData['startDt']
    endDt = lifeRateData['endDt']
    lifeKjPv = lifeRateData['lifeKjPv']
    lifeKjUv = lifeRateData['lifeKjUv']
    notLifeKjPv = lifeRateData['notLifeKjPv']
    notLifeKjUv = lifeRateData['notLifeKjUv']
    lifeKjTimes = round(lifeKjPv/lifeKjUv,2)
    notLifeKjTimes = round(notLifeKjPv/notLifeKjUv,2)
    lifeKjUpTimes = '%.2f' %(lifeKjTimes - notLifeKjTimes)
    lifeKjUpTimes7Day = round((lifeKjTimes - notLifeKjTimes)/7,2)
    print(f"每日开机频次提升-开始时间:{startDt},结束时间:{endDt},生活助手开机次数:{lifeKjPv},生活助手开机设备数:{lifeKjUv},非生活助手开机次数:{notLifeKjPv},非生活助手开机设备数:{notLifeKjUv}"
          f"生活助手开机频次:{lifeKjTimes},非生活助手开机频次:{notLifeKjTimes},提升频次:{lifeKjUpTimes},提升周频次:{lifeKjUpTimes7Day}")
    #print(lifeRateData)
    #print(dataJson)
    #报表key - 存储所有开机频次数据
    #analysisKey = "StartUpFrequencyAll"
    #saveJsonDb(analysisKey,'lifeKjPv','生活助手开机次数',lifeKjPv,endDt)
    #saveJsonDb(analysisKey, 'lifeKjUv', '生活助手总设备数', lifeKjUv, endDt)
    #saveJsonDb(analysisKey, 'notLifeKjPv', '非生活助手开机次数', notLifeKjPv, endDt)
    #saveJsonDb(analysisKey, 'notLifeKjUv', '非生活助手总设备数', notLifeKjUv, endDt)
    #saveJsonDb(analysisKey, 'lifeKjTimes', '生活助手开机频次', lifeKjTimes, endDt)
    #saveJsonDb(analysisKey, 'notLifeKjTimes', '非生活助手开机频次', notLifeKjTimes, endDt)
    #saveJsonDb(analysisKey, 'lifeKjUpTimes', '周提升频次', lifeKjUpTimes, endDt)
    #saveJsonDb(analysisKey, 'lifeKjUpTimes7Day', '每日提升频次', lifeKjUpTimes7Day, endDt)
    # 报表key - 存储 生活助手 跟 非生活助手 开机频次
    analysisKey = "StartUpFrequency"
    saveJsonDb(analysisKey, 'lifeKjRate', '生活助手', lifeKjTimes, endDt)
    saveJsonDb(analysisKey, 'notLifeKjRate', '大盘', notLifeKjTimes, endDt)

# 调用大数据接口 获取 生活助手付费率-周表
def lifeAssistantPayRateWeek():

    url = "http://192.168.2.176:8080/star-rocks/api/v1/query-service?token=93da758169824120a3087065"

    payload = json.dumps({
        "modelId": "lifeAssistantPayRateWeek",
        "requireParams": {
            "endDt": f"{today}"
        }
    })
    headers = {
        'Content-Type': 'application/json'
    }

    response = requests.request("POST", url, headers=headers, data=payload)

    dataJson = json.loads(response.text)
    lifeRateData = dataJson['data'][0]
    startDt = lifeRateData['startDt']
    endDt = lifeRateData['endDt']
    lifeTotalUv = lifeRateData['lifeTotalUv']
    lifePayUv = lifeRateData['lifePayUv']
    lifePayRate = lifeRateData['lifePayRate']
    lifePayRate = round(lifePayRate*100,2)
    notLifeTotalUv = lifeRateData['notLifeTotalUv']
    notLifePayUv = lifeRateData['notLifePayUv']
    notLifePayRate = lifeRateData['notLifePayRate']
    notLifePayRate = round(notLifePayRate * 100, 2)
    lifePayRateUp = lifeRateData['lifePayRateUp']
    lifePayRateUp = round(lifePayRateUp * 100, 2)

    print(f"每周生活助手付费率-开始时间:{startDt},结束时间:{endDt},生活助手总设备数:{lifeTotalUv},生活助手付费设备量:{lifePayUv},非生活助手总设备量:{notLifeTotalUv},非生活助手付费设备量:{notLifePayUv}"
          f"生活助手付费率:{lifePayRate},非生活助手付费率:{notLifePayRate},付费率提升:{lifePayRateUp}")
    #print(lifeRateData)
    #print(dataJson)
    #报表key - 存储所有付费率数据
    analysisKey = "lifeAssistantPayRateWeekAll"
    #saveJsonDb(analysisKey,'lifeTotalUv','生活助手总设备数',lifeTotalUv,endDt)
    #saveJsonDb(analysisKey, 'lifePayUv', '生活助手付费设备数', lifePayUv, endDt)
    #saveJsonDb(analysisKey, 'notLifeTotalUv', '非生活助手总设备数', notLifeTotalUv, endDt)
    #saveJsonDb(analysisKey, 'notLifePayUv', '非生活助手付费设备数', notLifePayUv, endDt)
    #saveJsonDb(analysisKey, 'lifePayRate', '生活助手付费率', lifePayRate, endDt)
    #saveJsonDb(analysisKey, 'notLifePayRate', '非生活助手付费率', notLifePayRate, endDt)
    #saveJsonDb(analysisKey, 'lifePayRateUp', '付费率提升', lifePayRateUp, endDt)
    # 报表key - 存储 生活助手 跟 非生活助手 付费率
    analysisKey = "lifeAssistantPayRateWeek"
    saveJsonDb(analysisKey, 'lifePayRate', '生活助手', lifePayRate, endDt)
    saveJsonDb(analysisKey, 'notLifePayRate', '大盘', notLifePayRate, endDt)

# 调用大数据接口 获取 生活助手付费金额-周表
def lifeAssistantArupWeek():

    url = "http://192.168.2.176:8080/star-rocks/api/v1/query-service?token=93da758169824120a3087065"

    payload = json.dumps({
        "modelId": "lifeAssistantArupWeek",
        "requireParams": {
            "endDt": f"{today}"
        }
    })
    headers = {
        'Content-Type': 'application/json'
    }

    response = requests.request("POST", url, headers=headers, data=payload)

    dataJson = json.loads(response.text)
    lifeRateData = dataJson['data'][0]
    startDt = lifeRateData['startDt']
    endDt = lifeRateData['endDt']
    lifePayUv = lifeRateData['lifePayUv']
    lifePayPrice = round(lifeRateData['lifePayPrice'],2)
    lifeArup = lifeRateData['lifeArup']
    lifeArup = round(lifeArup,2)
    notLifePayUv = lifeRateData['notLifePayUv']
    notLifePayPrice = round(lifeRateData['notLifePayPrice'],2)
    notLifeArup = lifeRateData['notLifeArup']
    notLifeArup = round(notLifeArup, 2)
    lifeArupUp = round((lifeArup-notLifeArup),2)

    print(f"每周生活助手付费率-开始时间:{startDt},结束时间:{endDt},生活助手付费设备量:{lifePayUv},生活助手付费金额:{lifePayPrice},非生活助手付费设备量:{notLifePayUv},非生活助手付费金额:{notLifePayPrice}"
          f"生活助手Arup:{lifeArup},非生活助手Arup:{notLifeArup},Arup提升:{lifeArupUp}")
    #print(lifeRateData)
    #print(dataJson)
    #报表key - 存储所有付费金额数据
    analysisKey = "lifeAssistantArupWeekAll"
    #saveJsonDb(analysisKey,'lifePayUv','生活助手付费设备量',lifePayUv,endDt)
    #saveJsonDb(analysisKey, 'lifePayPrice', '生活助手付费金额', lifePayPrice, endDt)
    #saveJsonDb(analysisKey, 'notLifePayUv', '非生活助手付费设备量', notLifePayUv, endDt)
    #saveJsonDb(analysisKey, 'notLifePayPrice', '非生活助手付费金额', notLifePayPrice, endDt)
    #saveJsonDb(analysisKey, 'lifeArup', '生活助手Arup', lifeArup, endDt)
    #saveJsonDb(analysisKey, 'notLifeArup', '非生活助手Arup', notLifeArup, endDt)
    #saveJsonDb(analysisKey, 'lifeArupUp', 'Arup提升', lifeArupUp, endDt)
    # 报表key - 存储 生活助手 跟 非生活助手 付费金额
    analysisKey = "lifeAssistantArupWeek"
    saveJsonDb(analysisKey, 'lifeArup', '生活助手', lifeArup, endDt)
    saveJsonDb(analysisKey, 'notLifeArup', '大盘', notLifeArup, endDt)

def saveJsonDb(analysisKey,fieldKey,fieldName,fieldValue,refDate):
    conn, cur = getDataConnection()
    msgDict = {}
    msgDict["field_key"] = fieldKey
    msgDict["field_name"] = fieldName
    msgDict["field_value"] = fieldValue
    msgJson = json.dumps(msgDict, ensure_ascii=False, sort_keys=False, separators=(',', ':'))

    # 插入
    insertSql = f"""insert into analysis_dynamic(analysis_key,msg,ref_date) 
                    value('{analysisKey}','{msgJson}','{refDate}')"""
    cur.execute(insertSql)
    conn.commit()

# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m-%d')
    return re_date

if __name__ == '__main__':
    today = getdate(4)
    lifeAssistantStartUpRatio()
    lifeAssistantStartUpFrequency()
    lifeAssistantPayRateWeek()
    lifeAssistantArupWeek()